package mtm.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.xml.stream.events.EndDocument;

import mtm.date.MtmMonth;
import mtm.date.MtmDate;
import mtm.ui.panel.MonthPanel;

public class DatabaseHandler {
	
	private final String CONNECTION_STRING = "jdbc:sqlite:mtm.db";
	
	private String dropTableJobQuery = "drop table if exists job";
	private String dropTableRepeatJobQuery = "drop table if exists repeat_job";
	private String createTableJobQuery = 
		"create table job(name, start, end, description, " +
		"id integer primary key, parent_id, icon, color)";
	private String createTableRepeatJobQuery = 
		"create table repeat_job(name, start, end, " +
		"description, pause, due_time, id integer primary key, elapse default 1, color)";
	
	
	private static DatabaseHandler instance = null;
	private Connection conn;
	private Statement stmt;
	private PreparedStatement ps;
	private ResultSet rs;
	
	private final boolean TEST = false;
	
	/**
	 * Return the singleton instance of class DatabaseHandler 
	 * @return
	 */
	public static DatabaseHandler getInstance() {
		return instance == null ? new DatabaseHandler() : instance;
	}
	
	
	/**
	 * Constructor
	 */
	private DatabaseHandler() {
		try {
			// connect to DB
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection(CONNECTION_STRING);
			instance = this;
			
			initDB();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	
	
	/**
	 * Init database
	 */
	private void initDB() {
		try {
			if (TEST) {
				stmt = conn.createStatement();

				// drop tables
				stmt.addBatch(dropTableJobQuery);
				stmt.addBatch(dropTableRepeatJobQuery);

				// create tables
				stmt.addBatch(createTableJobQuery);
				stmt.addBatch(createTableRepeatJobQuery);

				// execute and close
				stmt.executeBatch();
				stmt.close();
				
//				createSampleData();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * Create sample data to test
	 */
	private void createSampleData() {
		// jobs
		insertJob(new Job(
				"English exam", 
				new MtmDate(2010, 3, 1, 8, 30), 
				new MtmDate(2010, 3, 1, 9, 30), 
				"so bad feeling"));
		insertJob(new Job(
				"English exam 2", 
				new MtmDate(2010, 3, 1, 9, 0), 
				new MtmDate(2010, 3, 1, 9, 30), 
				"so bad feeling"));
		insertJob(new Job(
				"Lab AI", 
				new MtmDate(2010, 3, 10, 7, 0), 
				new MtmDate(2010, 3, 10, 9, 30), 
				"Ko dễ nuốt"));
		insertJob(new Job(
				"Bài tập ĐHMT", 
				new MtmDate(2010, 3, 11, 9, 0), 
				new MtmDate(2010, 3, 11, 10, 30), 
				"cũng ko dễ nuốt"));
		insertJob(new Job(
				"abc", 
				new MtmDate(2010, 3, 17, 9, 0), 
				new MtmDate(2010, 3, 17, 10, 30), 
				"cũng ko dễ nuốt"));
		insertJob(new Job(
				"def", 
				new MtmDate(2010, 3, 15, 9, 0), 
				new MtmDate(2010, 3, 15, 10, 30), 
				"cũng ko dễ nuốt"));
		insertJob(new Job(
				"Deadline assignment AI", 
				new MtmDate(2010, 3, 22), 
				new MtmDate(2010, 3, 22), 
				"have 2 weeks to complete"));
		
		// repeat jobs
		insertRepeatJob(new RepeatJob(
				"Internship", 
				new MtmDate(2010, 3, 1, 13, 0), 
				new MtmDate(2010, 3, 1, 17, 0), 
				"An event happens each 5 days", 
				RepeatJob.WEEK_LOOP, 
				new MtmDate(2010, 5, 1),
				5));
		insertRepeatJob(new RepeatJob(
				"Go to Thu Duc", 
				new MtmDate(2010, 1, 14, 8, 0), 
				new MtmDate(2010, 1, 14, 17, 0), 
				"An event happens each month", 
				RepeatJob.MONTH_LOOP, 
				null,
				2));
		insertRepeatJob(new RepeatJob(
				"Go home to enjoy summer holiday", 
				new MtmDate(2008, 6, 1, 0, 0), 
				new MtmDate(2008, 6, 1, 23, 59), 
				"An event happens each year", 
				RepeatJob.YEAR_LOOP, 
				null,
				7));
		insertRepeatJob(new RepeatJob(
				"An event elapses 3 days and pause 3 days", 
				new MtmDate(2010, 3, 1, 18, 0), 
				new MtmDate(2010, 3, 1, 20, 0), 
				"Blah blah ...", 
				6, 
				new MtmDate(2010, 5, 4),
				3));
	}
	
	
	/**
	 * Return list of jobs in a range of time
	 * @param start
	 * @param end
	 * @return
	 */
	public ArrayList<Job> getJobsInRange(MtmDate start, MtmDate end) {
		ArrayList<Job> result = new ArrayList<Job>();
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from job" +
					" where strftime('%Y-%m-%d %H:%M', start) >= '" + start.toString() +
					"' and strftime('%Y-%m-%d %H:%M', end) <= '" + end.toString() + "'");
			
			// add value to list
			Job job;
			while (rs.next()) {
				job = new Job();
				job.setJobName(rs.getString("name"));
				job.setStartTime(MtmDate.createFromString(rs.getString("start")));
				job.setEndTime(MtmDate.createFromString(rs.getString("end")));
				job.setDescription(rs.getString("description"));
				job.setIcon(rs.getString("icon"));
				job.setColor(rs.getInt("color"));
				job.setId(rs.getInt("id"));
				job.setParentId(rs.getInt("parent_id"));
				
				result.add(job);
			}
			
			// close
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		// add jobs from repeat job list
		result.addAll(getRepeatJobsInRange(start, end));
		
		return result;
	}
	
	
	/**
	 * Return jobs exist in a week
	 * @param firstDayOfWeek
	 * @return
	 */
	public ArrayList<Job> getJobsInWeek(MtmDate firstDayOfWeek) {
		MtmDate lastDayOfWeek = new MtmDate(firstDayOfWeek);
		// TODO check here
		lastDayOfWeek.addDate(6);
		lastDayOfWeek.setHours(23);
		lastDayOfWeek.setMinutes(59);
		
		return getJobsInRange(firstDayOfWeek, lastDayOfWeek);
	}
	
	
	/**
	 * Return jobs exist in a month
	 * @param month
	 * @return
	 */
	public ArrayList<Job> getJobsInMonth(int year, int month) {
		int numOfDatesInMonth = (new MtmMonth(month, year)).getNumberOfDays();
		return getJobsInRange(
				new MtmDate(year, month, 1), 
				new MtmDate(year, month, numOfDatesInMonth, 23, 59)
				);
	}
	
	
	/**
	 * Find a job which is duplicated time which the given job
	 * return null when find no such above jobs
	 * @param job
	 * @return
	 */
	public String findDuplicatedJobWith(Job job) {
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select name from job where " +
					" (strftime('%Y-%m-%d %H:%M', start) < '" + job.getEndTime().toString() + "' " +
					" or strftime('%Y-%m-%d %H:%M', end) < '" + job.getStartTime().toString() + "')");
			if (rs.next()) {
				// close 
				String duplicatedJobName = rs.getString("name");
				rs.close();
				stmt.close();
				
				return duplicatedJobName;
			}
			
			// close
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	
	/**
	 * Insert a job to database
	 * @return name of the duplicated job, or null if insert successfully
	 */
	public String insertJob(Job job) {
		try {
			
			// insert to DB
			ps = conn.prepareStatement("insert into job(name, start, end, description, parent_id, icon, color) " +
					"values(?, ?, ?, ?, ?, ?, ?)");
			ps.setString(1, job.getJobName());
			ps.setString(2, job.getStartTime().toString());
			ps.setString(3, job.getEndTime().toString());
			ps.setString(4, job.getDescription());
			ps.setInt(5, job.getParentId());
			ps.setString(6, job.getIcon());
			ps.setInt(7, job.getColor());

			ps.execute();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	
	/**
	 * Update a job
	 * @param job
	 */
	public void updateJob(Job job, int jobId) {
		try {
			ps = conn.prepareStatement(
					"update job set name = ?, start =?, end = ?, description = ?, icon = ?, color = ? " +
					"where id = ?");
			ps.setString(1, job.getJobName());
			ps.setString(2, job.getStartTime().toString());
			ps.setString(3, job.getEndTime().toString());
			ps.setString(4, job.getDescription());
			ps.setString(5, job.getIcon());
			ps.setInt(6, job.getColor());
			ps.setInt(7, jobId);
			
			ps.execute();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * Remove job
	 * @param job
	 */
	public void removeJob(int jobId) {
		try {
			ps = conn.prepareStatement("delete from job where id = ?");
			ps.setInt(1, jobId);
			
			ps.execute();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * Return list of jobs correlative with repeat jobs in a range of time
	 * @param start
	 * @param end
	 * @return
	 */
	public ArrayList<Job> getRepeatJobsInRange(MtmDate start, MtmDate end) {
		ArrayList<Job> result = new ArrayList<Job>();
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from repeat_job " +
				"where due_time is null " +
				"or strftime('%Y-%m-%d %H:%M', start) <= strftime('%Y-%m-%d %H:%M', due_time) "
				);
			
			// add value to list
			String jobName;
			String description;
			int pause;
			int elapse;
			int parentId;
			int color;
			MtmDate startTime;
			MtmDate endTime;
			MtmDate dueTime;
			
			Job job = null;			// temp job to insert into DB
			MtmDate jobEnd = null;	// temp end time of job, we will search for jobs from startTime to jobEnd
			
			while (rs.next()) {
				jobName = rs.getString("name");
				description = rs.getString("description");
				pause = rs.getInt("pause");
				elapse = rs.getInt("elapse");
				parentId = rs.getInt("id");
				color = rs.getInt("color");
				startTime = MtmDate.createFromString(rs.getString("start"));
				endTime = MtmDate.createFromString(rs.getString("end"));
				dueTime = MtmDate.createFromString(rs.getString("due_time"));
				
				// calculat jobEnd
				if (dueTime != null) {
					if (dueTime.compareTo(end) < 0) {
						jobEnd = dueTime;
					} else {
						jobEnd = end;
					}
				} else {
					jobEnd = end;
				}
				
				// TODO recalculate start time of job
				boolean isEndElapse = false;		// check for job has started a period
				if (startTime.compareTo(start) < 0) {
					// in case loop yearly
					if (pause == RepeatJob.YEAR_LOOP) {
						while (startTime.compareTo(start) < 0) {
							if (!isEndElapse) {
								startTime.addDate(elapse - 1);
							} else {
								startTime.addDate(1 - elapse);
								startTime.addYear(1);
							}
							
							isEndElapse = !isEndElapse;
						}
					}
					// in case loop monthly
					else if (pause == RepeatJob.MONTH_LOOP) {
						while (startTime.compareTo(start) < 0) {
							if (!isEndElapse) {
								startTime.addDate(elapse - 1);
							} else {
								startTime.addDate(1 - elapse);
								startTime.addMonth(1);
							}
							
							isEndElapse = !isEndElapse;
						}
					}
					// in case loop daily
					else if (pause == RepeatJob.DATE_LOOP) {
						startTime = new MtmDate(start);
					} 
					// in case loop weekly or custom days 
					else {
						while (startTime.compareTo(start) < 0) {
							if (!isEndElapse) {
								startTime.addDate(elapse - 1);
							} else {
								startTime.addDate(pause + 1 - elapse);
							}
							
							isEndElapse = !isEndElapse;
						}
					} 
				} else {
//					isEndElapse = true;
				}
				
				// check for startTime of job now is end an elapse period or start a new period
				if (isEndElapse) {
					// in case startTime > jobEnd move to next loop (while(rs.next()) loop)
					if (startTime.compareTo(jobEnd) > 0) {
						continue;
					}
					
					// clone the start of selected range time
					MtmDate oldStart = new MtmDate(start);
					
					// change time of startDate
					oldStart.setHours(startTime.getHours());
					oldStart.setMinutes(startTime.getMinutes());
					
					// change date of endTime to the same as start of job
					endTime.setYear(oldStart.getYear());
					endTime.setMonth(oldStart.getMonth());
					endTime.setDate(oldStart.getDate());
					
					// collect jobs from start to startTime 
					while (oldStart.compareTo(startTime) <= 0) {
						job = new Job();
						job.setJobName(jobName);
						job.setStartTime(new MtmDate(oldStart));
						job.setEndTime(new MtmDate(endTime));
						job.setDescription(description);
						job.setColor(color);
						job.setParentId(parentId);
						
						result.add(job);
						
						oldStart.addDate(1);
						endTime.addDate(1);
					}
					
					// increase startTime to start a period
					switch (pause) {
					case RepeatJob.YEAR_LOOP:
						startTime.addDate(1 - elapse);
						startTime.addYear(1);
						break;
						
					case RepeatJob.MONTH_LOOP:
						startTime.addDate(1 - elapse);
						startTime.addMonth(1);
						break;
						
					default:
						startTime.addDate(pause + 1 - elapse);
						break;
					}
					
					// in case startTime > jobEnd move to next loop (while(rs.next()) loop)
					if (startTime.compareTo(jobEnd) > 0) {
						continue;
					}
				} 
				
				// reset endTime
				endTime.setYear(startTime.getYear());
				endTime.setMonth(startTime.getMonth());
				endTime.setDate(startTime.getDate());
				
				// create list
				int count = 0;		// to count number of days this job has passed
				switch(pause) {
				case RepeatJob.DATE_LOOP:
					while (startTime.compareTo(jobEnd) <= 0) {
						job = new Job();
						job.setJobName(jobName);
						job.setStartTime(new MtmDate(startTime));
						job.setEndTime(new MtmDate(endTime));
						job.setDescription(description);
						job.setColor(color);
						job.setParentId(parentId);
						
						result.add(job);
						
						startTime.addDate(1);
						endTime.addDate(1);
					}
					break;
					
				case RepeatJob.MONTH_LOOP:
					{
						while (startTime.compareTo(jobEnd) <= 0) {
							job = new Job();
							job.setJobName(jobName);
							job.setStartTime(new MtmDate(startTime));
							job.setEndTime(new MtmDate(endTime));
							job.setDescription(description);
							job.setColor(color);
							job.setParentId(parentId);
							
							result.add(job);
							
							if (++count == elapse) {
								count = 0;
								startTime.addDate(1 - elapse);
								startTime.addMonth(1);
								endTime.addDate(1 - elapse);
								endTime.addMonth(1);
							} else {
								startTime.addDate(1);
								endTime.addDate(1);
							}
						}
					}
					break;
					
				case RepeatJob.YEAR_LOOP:
					{
						while (startTime.compareTo(jobEnd) <= 0) {
							job = new Job();
							job.setJobName(jobName);
							job.setStartTime(new MtmDate(startTime));
							job.setEndTime(new MtmDate(endTime));
							job.setDescription(description);
							job.setColor(color);
							job.setParentId(parentId);
							
							result.add(job);
							
							if (++count == elapse) {
								count = 0;
								startTime.addDate(1 - elapse);
								startTime.addYear(1);
								endTime.addDate(1 - elapse);
								endTime.addYear(1);
							} else {
								startTime.addDate(1);
								endTime.addDate(1);
							}
						}
					}
					break;
					
				default:
					while (startTime.compareTo(jobEnd) <= 0) {
						job = new Job();
						job.setJobName(jobName);
						job.setStartTime(new MtmDate(startTime));
						job.setEndTime(new MtmDate(endTime));
						job.setDescription(description);
						job.setColor(color);
						job.setParentId(parentId);
						
						result.add(job);
						
						if (++count == elapse) {
							count = 0;
							startTime.addDate(1 + pause - elapse);
							endTime.addDate(1 + pause - elapse);
						} else {
							startTime.addDate(1);
							endTime.addDate(1);
						}
					}
					break;
				}
			}
			
			// close
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	
	/**
	 * Return list of jobs correlative with repeat jobs in a month
	 * @param year
	 * @param month
	 * @return
	 */
	public ArrayList<Job> getRepeatJobsInMonth(int year, int month) {
		int numOfDatesInMonth = (new MtmMonth(month, year)).getNumberOfDays();
		MtmDate lastDate = new MtmDate(year, month, 1);
		lastDate.addDate(numOfDatesInMonth);
		return getRepeatJobsInRange(
				new MtmDate(year, month, 1), 
//				new MtmDate(year, month, numOfDatesInMonth)
				lastDate
				);
	}
	
	
	/**
	 * Return a RepeatJob based on its ID
	 * @param id
	 * @return
	 */
	public RepeatJob getRepeatJob(int id) {
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select name, start, end, " +
			"description, pause, due_time, id, elapse, color from repeat_job " +
			"where id = " + id);
			
			while (rs.next()) {
				RepeatJob repeatJob = new RepeatJob();
				repeatJob.setJobName(rs.getString("name"));
				repeatJob.setStartTime(MtmDate.createFromString(rs.getString("start")));
				repeatJob.setEndTime(MtmDate.createFromString(rs.getString("end")));
				repeatJob.setDescription(rs.getString("description"));
				repeatJob.setPause(rs.getInt("pause"));
				repeatJob.setDueTime(MtmDate.createFromString(rs.getString("due_time")));
				repeatJob.setId(id);
				repeatJob.setElapse(rs.getInt("elapse"));
				repeatJob.setColor(rs.getInt("color"));
				
				// important note: must closer ResultSet and Statement before using DB
				// to other purpost
				rs.close();
				stmt.close();
				
				return repeatJob;
			}
			
			// close
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}


	/**
	 * Insert a repeat job to database
	 * @param job
	 * @return return Id of inserted repeat job
	 */
	public int insertRepeatJob(RepeatJob job) {
		try {
//			job.setId(++jobId);
			ps = conn.prepareStatement("insert into repeat_job(name, start, end, description, pause, due_time, elapse, color) values" +
					"(?, ?, ?, ?, ?, ?, ?, ?)");
			ps.setString(1, job.getJobName());
			ps.setString(2, job.getStartTime().toString());
			ps.setString(3, job.getEndTime().toString());
			ps.setString(4, job.getDescription());
			ps.setInt(5, job.getPause());
			ps.setString(6, job.getDueTime() != null ? job.getDueTime().toString() : null);
			ps.setInt(7, job.getElapse());
			ps.setInt(8, job.getColor());
			
			ps.execute();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return job.getId();
	}
	
	
	/**
	 * Update repeat job
	 * @param job
	 */
	public void updateRepeatJob(RepeatJob job, int jobId) {
		try {
			ps = conn.prepareStatement("update repeat_job set name = ?, start = ?, " +
					"end = ?, description = ?, pause = ?, due_Time = ?, elapse = ?, " +
					"color = ? where id = ?");
			ps.setString(1, job.getJobName());
			ps.setString(2, job.getStartTime().toString());
			ps.setString(3, job.getEndTime().toString());
			ps.setString(4, job.getDescription());
			ps.setInt(5, job.getPause());
			ps.setString(6, (job.getDueTime() != null ? job.getDueTime().toString() : null));
			ps.setInt(7, job.getElapse());
			ps.setInt(8, job.getColor());
			ps.setInt(9, jobId);
			
			ps.execute();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * Remove a repeat job
	 * @param job
	 */
	public void removeRepeatJob(int jobId) {
		try {
			ps = conn.prepareStatement("delete from repeat_job where id = ?");
			ps.setInt(1, jobId);
			
			ps.execute();
			ps.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
